﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using PetaPoco;
using Shop.Wares.Models;

namespace Shop.Home.Repositories
{
    public class HomeAccess
    {
        private Database DB = new Database("ConnString");
        /// <summary>
        /// 获取一个类别下销售量前几的商品信息
        /// </summary>
        /// <param name="Top">前几？</param>
        /// <param name="CID">类别ID</param>
        /// <returns></returns>
        public IList<Wares.Models.Wares> GetTopWInfo(int Top, int CID)
        {
            string strq = 
                string.Format("select top {0} * from Shop_Wares " +
                "where CID = {1} " +
                "and Status = 0 " +
                "order by SaleNum desc", Top, CID);
            Sql sql = Sql.Builder.Append(strq);
            return DB.Fetch<Wares.Models.Wares>(sql);
        }
        /// <summary>
        /// 获取相应类别最新的商品
        /// </summary>
        /// <param name="Top"></param>
        /// <param name="CID"></param>
        /// <param name="SCID"></param>
        /// <returns></returns>
        public IList<Wares.Models.Wares> GetNowWInfo(int Top,int CID,int SCID)
        {
            string strsql = 
                string.Format("select top {0} * from Shop_Wares " +
                "where Status = 0", Top);
            if (SCID != 0 && CID == 0)
            {
                strsql += string.Format(" and SCID = {0}", SCID);
            }

            if (SCID != 0 || CID != 0)
            {
                strsql += string.Format(" and CID = {0}", CID);
            }
            strsql += "order by OrderTime desc";
            Sql sql = Sql.Builder.Append(strsql);
            return DB.Fetch<Wares.Models.Wares>(sql);
        }
        /// <summary>
        /// 前台获取类别内商品信息
        /// </summary>
        /// <param name="page">第几页</param>
        /// <param name="itemsPerPage">每页多少行</param>
        /// <param name="CID">类别ID</param>
        /// <param name="SCID">子类别ID</param>
        /// <param name="WName">商品名</param>
        /// <param name="OrderBy">排序字段名</param>
        /// <param name="Lift">升降序标志，0升序，1降序DESC</param>
        /// <returns></returns>
        public Page<Shop.Wares.Models.Wares> GetHomeSerchWaresInfo(long page, long itemsPerPage, int CID, int SCID, string WName, string OrderBy, int Lift)
        {
            string strsql = string.Format("select *  from Shop_Wares where Status = 0 ");
            if (CID != 0)
            {
                strsql += string.Format(" and CID = {0} ", CID);
            }
            if (SCID != 0)
            {
                strsql += string.Format(" and SCID = {0} ", SCID);
            }
            if (!string.IsNullOrEmpty(WName))
            {
                strsql += string.Format(" and WName like '%{0}%' ", WName);
            }
            if (Lift == 0)
            {
                strsql += string.Format(" order by {0}", OrderBy);
            }
            else
            {
                strsql += string.Format("order by {0} desc", OrderBy);
            }

            Sql sql = Sql.Builder.Append(strsql);
            return DB.Page<Shop.Wares.Models.Wares>(page, itemsPerPage, sql);
        }
        /// <summary>
        /// 根据CID获取类别信息
        /// </summary>
        /// <param name="CID"></param>
        /// <returns></returns>
        public Shop.Wares.Models.WaresCategory GetWCInfo(int CID)
        {
            Sql sql = Sql.Builder.Append("select * from Shop_WaresCategory where CID = @0", CID);
            return DB.FirstOrDefault<WaresCategory>(sql);
        }
        /// <summary>
        /// 根据SCID获取类别信息
        /// </summary>
        /// <param name="SCID"></param>
        /// <returns></returns>
        public Shop.Wares.Models.SonWaresCategory GetSWCInfo(int SCID)
        {
            Sql sql = Sql.Builder.Append("select * from Shop_SonWaresCategory where SCID = @0", SCID);
            return DB.FirstOrDefault<SonWaresCategory>(sql);
        }
        /// <summary>
        /// 根据WID获取商品信息
        /// </summary>
        /// <param name="WID"></param>
        /// <returns></returns>
        public Shop.Wares.Models.AnyWares GetWInfo(int WID)
        {
            Sql sql = Sql.Builder.Append("select Shop_Wares.*,Shop_WaresCategory.CName,Shop_SonWaresCategory.SCName from Shop_Wares join Shop_WaresCategory on Shop_Wares.CID = Shop_WaresCategory.CID join Shop_SonWaresCategory on Shop_Wares.SCID = Shop_SonWaresCategory.SCID where Shop_Wares.WID = @0", WID);
            return DB.FirstOrDefault<Shop.Wares.Models.AnyWares>(sql);
        }
    }
}